package com.san.metadata.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.san.base.dao.BaseManager;
import com.san.common.datasource.JdbcManager;
import com.san.common.utils.CTConversionUtils;
import com.san.common.utils.Constant;
import com.san.metadata.domain.DataSourceDO;
import com.san.metadata.domain.TechnicalTableDO;
import com.san.metadata.vo.TechnicalColumnsVO;

@Service
public class TechnicalMetadataManager extends BaseManager {
	
	private static final Logger logger = LoggerFactory.getLogger(TechnicalMetadataManager.class);

	/**
	 * 查询数据源下的表
	 * 
	 * @param dataSourceDO 数据源
	 * @return
	 */
	public List<Map<String, Object>> listSourceTable(DataSourceDO dataSourceDO) {
		JdbcManager jdbcManager = new JdbcManager();
		String sql = "";
		List<String> requestlist = null;
		Object[] obj = null;
		List<Map<String, Object>> list = null;
		List<Map<String, Object>> resultList = null;
		Map<String, Object> resultMap = null;
		DruidPooledConnection conn = null;
		try {
			conn = this.getDBConnection(dataSourceDO.getSourceCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		switch (dataSourceDO.getSourceType()) {
		case Constant.SOURCE_TYPE_ORACLE:
			sql = "select table_name,comments from user_tab_comments where table_type = 'TABLE' ";
			list = jdbcManager.executeQuery(conn, sql, obj);
			resultList = new ArrayList<Map<String, Object>>();
			for (Map<String, Object> map : list) {
				resultMap = new HashMap<String, Object>();
				resultMap.put("tableName", map.get("table_name"));
				resultMap.put("tableComment", map.get("comments"));
				resultList.add(resultMap);
			}

			break;
		case Constant.SOURCE_TYPE_MYSQL:
			sql = "select table_name,table_comment from information_schema.tables where table_schema= ? ";
			requestlist = new ArrayList<String>();
			requestlist.add(dataSourceDO.getDbName());
			obj = requestlist.toArray();
			list = jdbcManager.executeQuery(conn, sql, obj);
			resultList = new ArrayList<Map<String, Object>>();
			for (Map<String, Object> map : list) {
				resultMap = new HashMap<String, Object>();
				resultMap.put("tableName", map.get("table_name"));
				resultMap.put("tableComment", map.get("table_comment"));
				resultList.add(resultMap);
			}
			break;
		default:
			break;
		}

		return resultList;
	}

	/**
	 * 查询数据源下的表的字段信息
	 * 
	 * @param dataSourceDO     数据源
	 * @param technicalTableDO 技术元数据
	 * @return
	 */
	public List<Map<String, Object>> listColumns(DataSourceDO dataSourceDO, TechnicalTableDO technicalTableDO) {
		String sql = "";
		List<String> requestlist = null;
		Object[] obj = null;
		List<Map<String, Object>> list = null;
		Map<String, Object> resultMap = null;
		List<Map<String, Object>> resultList = null;
		JdbcManager jdbcManager = new JdbcManager();
		DruidPooledConnection conn = null;
		try {
			conn = this.getDBConnection(dataSourceDO.getSourceCode());
		} catch (Exception e) {
			e.printStackTrace();
		}
		switch (dataSourceDO.getSourceType()) {
		case Constant.SOURCE_TYPE_ORACLE:
			sql = "select a.COLUMN_NAME," + "       DATA_TYPE," + "       DATA_LENGTH," + "		  DATA_PRECISION,"
					+ "       DATA_SCALE," + "       NULLABLE," + "       COLUMN_ID," + "       COMMENTS,"
					+ "       CONSTRAINT_TYPE" + "  from (SELECT USER_TAB_COLS.TABLE_NAME,"
					+ "               USER_TAB_COLS.COLUMN_NAME," + "               USER_TAB_COLS.DATA_TYPE,"
					+ "               USER_TAB_COLS.DATA_LENGTH," + "               USER_TAB_COLS.DATA_PRECISION,"
					+ "               USER_TAB_COLS.DATA_SCALE," + "               USER_TAB_COLS.NULLABLE,"
					+ "               USER_TAB_COLS.COLUMN_ID," + "               user_col_comments.COMMENTS"
					+ "          FROM USER_TAB_COLS" + "          left join user_col_comments"
					+ "            on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME"
					+ "           and user_col_comments.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME"
					+ "         where USER_TAB_COLS.TABLE_NAME = ?) a"
					+ "  left join (select col.COLUMN_NAME，con.CONSTRAINT_TYPE"
					+ "               from user_constraints con, user_cons_columns col"
					+ "              where con.constraint_name = col.constraint_name"
					+ "                and con.constraint_type = 'P'" + "                and col.table_name = ?) b"
					+ "    on b.COLUMN_NAME = a.COLUMN_NAME order by COLUMN_ID" + "";
			requestlist = new ArrayList<String>();
			requestlist.add(technicalTableDO.getSourceTableName());
			requestlist.add(technicalTableDO.getSourceTableName());
			obj = requestlist.toArray();
			list = jdbcManager.executeQuery(conn, sql, obj);
			resultList = new ArrayList<Map<String, Object>>();
			for (Map<String, Object> map : list) {
				resultMap = new HashMap<String, Object>();
				resultMap.put("columnName", map.get("column_name"));
				resultMap.put("columnType", map.get("data_type"));
				resultMap.put("columnLength", map.get("data_length"));
				resultMap.put("columnPrecision", map.get("data_precision"));
				resultMap.put("columnScale", map.get("data_scale"));
				resultMap.put("columnIsNullable", ("Y".equals(map.get("nullable")) ? "YES" : "NO"));
				resultMap.put("orderNum", map.get("column_id"));
				resultMap.put("columnComment", map.get("comments"));
				resultMap.put("columnIsKey", ("P".equals(map.get("constraint_type")) ? "YES" : "NO"));
				resultList.add(resultMap);
			}
			break;
		case Constant.SOURCE_TYPE_MYSQL:
			sql = "SELECT" + "	COLUMN_NAME," + "	IS_NULLABLE," + "	DATA_TYPE," + "	CHARACTER_MAXIMUM_LENGTH,"
					+ "	NUMERIC_PRECISION," + "	NUMERIC_SCALE," + "	COLUMN_KEY," + "	COLUMN_COMMENT,"
					+ "	ORDINAL_POSITION" + " FROM " + "	INFORMATION_SCHEMA. COLUMNS" + " WHERE"
					+ "	TABLE_SCHEMA = ? " + "AND TABLE_NAME = ? " + "ORDER BY" + "	ORDINAL_POSITION";
			requestlist = new ArrayList<String>();
			requestlist.add(dataSourceDO.getDbName());
			requestlist.add(technicalTableDO.getSourceTableName());
			obj = requestlist.toArray();
			list = jdbcManager.executeQuery(conn, sql, obj);
			resultList = new ArrayList<Map<String, Object>>();
			for (Map<String, Object> map : list) {
				resultMap = new HashMap<String, Object>();
				resultMap.put("columnName", map.get("column_name"));
				resultMap.put("columnType", map.get("data_type"));
				resultMap.put("columnLength", map.get("character_maximum_length"));
				resultMap.put("columnPrecision", map.get("numeric_precision"));
				resultMap.put("columnScale", map.get("numeric_scale"));
				resultMap.put("columnIsNullable", map.get("is_nullable"));
				resultMap.put("orderNum", map.get("ordinal_position"));
				resultMap.put("columnComment", map.get("column_comment"));
				resultMap.put("columnIsKey", ("PRI".equals(map.get("column_key")) ? "YES" : "NO"));
				resultList.add(resultMap);
			}
			break;
		default:
			break;
		}
		return resultList;
	}

	/**
	 * 在目标源中建表
	 * 
	 * @param sourceType             数据源类型
	 * @param technicalTableDO       技术元数据
	 * @param targetDataSourceDO     目标数据源
	 * @param technicalColumnsVOList 技术元数据字段信息List
	 * @return
	 */
	public boolean saveTable(int sourceType, TechnicalTableDO technicalTableDO, DataSourceDO targetDataSourceDO,
			List<TechnicalColumnsVO> technicalColumnsVOList) {
		JdbcManager jdbcManager = new JdbcManager();
		DruidPooledConnection conn = null;
		try {
			conn = this.getDBConnection(targetDataSourceDO.getSourceCode());
		} catch (Exception e) {
			e.printStackTrace();
		}

		StringBuffer sql = new StringBuffer();
		StringBuffer sqlComment = new StringBuffer();
		boolean flag = false;
		Object[] obj = null;
		Integer j = 0;
		switch (targetDataSourceDO.getSourceType()) {
		case Constant.SOURCE_TYPE_ORACLE: {
			if (sourceType == Constant.SOURCE_TYPE_ORACLE) {
				sql.append("create table ").append(technicalTableDO.getTargetTableName()).append("").append("(");
				sqlComment.append("");
				for (TechnicalColumnsVO i : technicalColumnsVOList) {
					j++;
					sql.append(" ").append(i.getColumnName()).append(" ") // 字段名
							.append(CTConversionUtils.Complete(i.getColumnType(), i.getColumnLength(),
									i.getColumnPrecision(), i.getColumnScale())) // 字段类型
							.append(" ").append(("YES".equals(i.getColumnIsKey()) ? "primary key" : "")) // 是否主键
							.append(" ").append(("YES".equals(i.getColumnIsNullable()) ? " NULL " : " NOT NULL ")) // 是否为空
							.append((j < technicalColumnsVOList.size() ? "," : "")); // 字段注释
				}
				sql.append(") ").append("\n\r");
				flag = jdbcManager.executeSQL(conn, sql.toString(), obj);
				if (flag) {
					if (technicalTableDO.getTargetTableComment() != null
							&& !"".equals(technicalTableDO.getTargetTableComment())) {
						sql.append("COMMENT ON table ").append(technicalTableDO.getTargetTableName()).append(" IS '")
								.append(technicalTableDO.getTargetTableComment()).append("' \n\r"); // 表注释
						flag = jdbcManager.executeSQL(conn, sql.toString(), obj);
					}
					for (TechnicalColumnsVO i : technicalColumnsVOList) {
						if (i.getColumnComment() != null && !"".equals(i.getColumnComment()) && flag) {
							sqlComment.append("comment on column ").append(technicalTableDO.getTargetTableName())
									.append(".").append(i.getColumnName()).append(" is '").append(i.getColumnComment())
									.append("' \n\r");
							flag = jdbcManager.executeSQL(conn, sqlComment.toString(), obj);
						}
					}
				}
			}
			if (sourceType == Constant.SOURCE_TYPE_MYSQL) {
				sql.append("create table ").append(technicalTableDO.getTargetTableName()).append("").append("(");
				sqlComment.append("");
				for (TechnicalColumnsVO i : technicalColumnsVOList) {
					j++;
					sql.append(" ").append(i.getColumnName()).append(" ") // 字段名
							.append(CTConversionUtils.M2O(i.getColumnType(), i.getColumnLength(),
									i.getColumnPrecision(), i.getColumnScale())) // 字段类型
							.append(" ").append(("YES".equals(i.getColumnIsKey()) ? "primary key" : "")) // 是否主键
							.append(" ").append(("YES".equals(i.getColumnIsNullable()) ? " NULL " : " NOT NULL ")) // 是否为空
							.append((j < technicalColumnsVOList.size() ? "," : "")); // 字段注释
				}
				sql.append(") ").append("\n\r");
				flag = jdbcManager.executeSQL(conn, sql.toString(), obj);
				if (flag) {
					if (technicalTableDO.getTargetTableComment() != null
							&& !"".equals(technicalTableDO.getTargetTableComment())) {
						sql.append("COMMENT ON table ").append(technicalTableDO.getTargetTableName()).append(" IS '")
								.append(technicalTableDO.getTargetTableComment()).append("' \n\r"); // 表注释
						flag = jdbcManager.executeSQL(conn, sql.toString(), obj);
					}
					for (TechnicalColumnsVO i : technicalColumnsVOList) {
						if (i.getColumnComment() != null && !"".equals(i.getColumnComment()) && flag) {
							sqlComment.append("comment on column ").append(technicalTableDO.getTargetTableName())
									.append(".").append(i.getColumnName()).append(" is '").append(i.getColumnComment())
									.append("' \n\r");
							flag = jdbcManager.executeSQL(conn, sqlComment.toString(), obj);
						}
					}
				}
			}
		}
			break;
		case Constant.SOURCE_TYPE_MYSQL: {
			if (sourceType == Constant.SOURCE_TYPE_ORACLE) {
				sql.append("CREATE TABLE `").append(technicalTableDO.getTargetTableName()).append("` ("); // 表名
				for (TechnicalColumnsVO i : technicalColumnsVOList) {
					j++;
					sql.append("`").append(i.getColumnName()).append("` ") // 字段名
							.append(CTConversionUtils.O2M(i.getColumnType(), i.getColumnLength(),
									i.getColumnPrecision(), i.getColumnScale()))
							.append(" ").append(("YES".equals(i.getColumnIsNullable()) ? " NULL" : " NOT NULL ")) // 是否为空
							.append(("YES".equals(i.getColumnIsKey()) ? "primary key" : "")) // 是否主键
							.append(((i.getColumnComment() != null && !"".equals(i.getColumnComment()))
									? (" COMMENT '" + i.getColumnComment() + "'")
									: " "))
							.append((j < technicalColumnsVOList.size() ? "," : "")); // 字段注释
				}
				sql.append(") ENGINE=InnoDB COMMENT='").append(technicalTableDO.getTargetTableComment()).append("';"); // 表注释
				flag = jdbcManager.executeSQL(conn, sql.toString(), obj);
			}
			if (sourceType == Constant.SOURCE_TYPE_MYSQL) {
				sql.append("CREATE TABLE `").append(technicalTableDO.getTargetTableName()).append("` ("); // 表名
				for (TechnicalColumnsVO i : technicalColumnsVOList) {
					j++;
					sql.append("`").append(i.getColumnName()).append("` ") // 字段名
							.append(CTConversionUtils.Complete(i.getColumnType(), i.getColumnLength(),
									i.getColumnPrecision(), i.getColumnScale()))
							.append(" ").append(("YES".equals(i.getColumnIsNullable()) ? " NULL " : " NOT NULL ")) // 是否为空
							.append(("YES".equals(i.getColumnIsKey()) ? "primary key" : "")) // 是否主键
							.append(((i.getColumnComment() != null && !"".equals(i.getColumnComment()))
									? (" COMMENT '" + i.getColumnComment() + "'")
									: " "))
							.append((j < technicalColumnsVOList.size() ? "," : "")); // 字段注释
				}
				sql.append(") ENGINE=InnoDB COMMENT='").append(technicalTableDO.getTargetTableComment() + "';"); // 表注释
				flag = jdbcManager.executeSQL(conn, sql.toString(), obj);
			}
		}
			break;
		default:
			break;
		}
		logger.info(sql.toString());
		return flag;
	}

}
